﻿Imports System.Data.OleDb

Public Class QuestionTagGateway
    Inherits TagGateway

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM QuestionTag LEFT JOIN SiteUser " &
                   "ON QuestionTag.UserID=SiteUser.ID " &
                   "WHERE QuestionTag.ID=@ID"
        End Get
    End Property

    ''' <summary>
    ''' Returns a data table with tags and their weights
    ''' </summary>
    ''' <returns>DataTable</returns>
    ''' <remarks>
    ''' Tag cloud algorithm pseudo code:
    '''     log(items) / log(items in biggest tag + 1) * (max - min) + min
    ''' was adopted by a stackoverflow article and can be found at:
    ''' http://stackoverflow.com/questions/3717314/what-is-the-formula-to-calculate-the-font-size-for-tags-in-a-tagcloud
    ''' The version of the algorithm in FishTank DB adds +1 to the divisor
    ''' to avoid division by zero problems.
    ''' </remarks>
    Public Function FindTagsWithWeights() As DataTable
        Dim sqlQuery As String = "SELECT * FROM QuestionTagWeightsView"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@MaxFontSize", TagUtils.MaxFontSize)
        queryParams.Add("@MinFontSize", TagUtils.MinFontSize)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function FindQuestionTags(ByVal questionID As ULong) As DataTable
        Dim sqlQuery As String = "SELECT * FROM QuestionTagsView WHERE ID=@QuestionID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@QuestionID", questionID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function GetQuestionTagsList(ByVal questionID As ULong) As ArrayList
        Dim tags As New ArrayList()
        For Each Row As DataRow In FindQuestionTags(questionID).Rows
            tags.Add(Row.Item("Title"))
        Next
        Return tags
    End Function


    Public Function FindByQuestionID(ByVal questionID As ULong) As DataTable
        Dim sqlQuery As String = "SELECT * FROM QuestionTag WHERE QuestionID=@QuestionID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@QuestionID", questionID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function FindByTagID(ByVal tagID As ULong) As DataTable
        Dim sqlQuery As String = "SELECT * FROM QuestionTag WHERE TagID=@TagID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@TagID", tagID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function FindByCompositeID(ByVal questionID As ULong, ByVal tagID As ULong) As DataTable
        Dim sqlQuery As String = "SELECT * FROM QuestionTag " &
                                 "WHERE QuestionId=@QuestionID AND TagID=@TagID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@QuestionID", questionID)
        queryParams.Add("@TagID", tagID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Sub DeleteByCompositeID(ByVal questionID As ULong, ByVal tagID As ULong)
        Dim sqlQuery As String = "DELETE FROM QuestionTag " &
                                 "WHERE QuestionId=@QuestionID AND TagID=@TagID"
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@QuestionID", questionID)
            command.Parameters.AddWithValue("@TagID", tagID)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

    Public Sub Update(ByVal questionID As ULong, ByVal tagList As ArrayList, ByVal userID As ULong)
        ' First delete the tags that do not appear in the updated tag list
        For Each tagRow As DataRow In FindQuestionTags(questionID).Rows
            If Not tagList.Contains(tagRow("Title")) Then
                DeleteByCompositeID(questionID, tagRow("TagID"))
            End If
        Next
        ' Insert new question tags
        For Each tag As String In tagList
            ' Ignores empty tags
            If Not String.IsNullOrWhiteSpace(tag) Then
                ' Convert tag to title case (indirectly trims extra white space)
                Dim tagTitle As String = TextUtils.ToTitleCase(tag)
                Dim tagTable As DataTable = FindWhere(New FilterCriterion("Title", tagTitle))
                Dim tagID As ULong

                If tagTable.Rows.Count = 0 Then
                    ' Insert new tag in Tag table
                    tagID = Insert(tagTitle)
                Else
                    ' Tag already exists in Tag table
                    tagID = tagTable.Rows(0)("ID")
                End If

                ' If questiong is not taged with this tag the insert new record
                Dim questionTagTable As DataTable = FindByCompositeID(questionID, tagID)
                If questionTagTable.Rows.Count = 0 Then
                    InsertQuestionTag(questionID, tagID, userID)
                End If
            End If
        Next

    End Sub

    Protected Sub InsertQuestionTag(ByVal questionID As ULong, ByVal tagID As ULong, ByVal userID As ULong)
        Dim sqlQuery As String = "INSERT INTO QuestionTag (QuestionID, TagID, UserID) VALUES (@QuestionID, @TagID, @UserID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@QuestionID", questionID)
                command.Parameters.AddWithValue("@TagID", tagID)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub

End Class
